-- 车辆信息
with shipment_cars as (
    select
           t.shipment_id    --任务ID
          ,t.shipment_no --任务编号
          ,t.shipment_name --任务名称
          ,t.plate_number--车牌号
          ,max(t.vehicletype_name) as  vehicletype_name--车型
          ,max(t.trailer_number) as trailer_number--挂箱
          ,max(t.vehicleline_type) as vehicleline_type --线路类型
          ,max(t.vehicle_volume) as vehicle_volume --车辆荷载体积
          ,max(t.load_weight) as load_weight--车辆荷载重量
          ,max(t.carrier_name) as carrier_name --承运商
          ,max(t.carrier_id) as carrier_id --承运商ID
          ,max(t.driver_name) as driver_name  --主驾驶员
          ,max(t.driver_contact) as driver1_tel --主驾驶员电话
          ,max(t.shipno_planned_departure_time ) as shipno_planned_departure_time -- 始发站计划发车时间
          ,max(t.shipno_actual_departure_time) as shipno_actual_departure_time --实际发车时间
          ,max(t.shipno_planned_arrival_time) as shipno_planned_arrival_time --规划到车时间
          ,max(t.shipno_actual_arrival_time) as shipno_actual_arrival_time --实际到车时间
          ,max(t.shipment_state) as shipment_state--批次运行状态
          ,max(business_attribute) as is_over_time  --是否加班
from jms_dwd.dwd_wide_tms_road_waybill_dt t
where t.dt between date_add('{{ execution_date | cst_ds }}', -6) AND '{{ execution_date | cst_ds }}'
      and t.plate_number is not null
    group by t.shipment_id,t.shipment_no,t.shipment_name,t.plate_number
),
shipment_stop  as (
select
           t.shipment_id    --任务ID
          ,t.shipment_no --任务编号
          ,t.shipment_name --任务名称
          ,t.start_site_code
          ,t.start_site_name
          ,t.end_site_code
          ,t.end_site_name
          ,t.roadsection_id --路段编号
          ,max(t.road_mileage) as  road_mileage --规划里程(KM)
          ,if(count(if(t.start_site_code is not null ,t.billcode,null))>0,0,1) as is_emport  --是否空车
          ,cast(sum(t.package_total_weight)*100/max(t.load_weight) as double) as loadrate_weight  --实际装载率（按重量）
          ,cast(sum(t.package_total_volume)*100/max(t.vehicle_volume) as double) as loadrate_volume --实际装载率（按体积）
          ,max(t.planned_departure_time) as  planned_departure_time--计划发车时间
          ,max(t.actual_departure_time ) as actual_departure_time --实际发车时间
          ,max(t.plan_driving_time	) as plan_driving_time	--计划在途时间
          ,max(t.actual_driving_time ) as  actual_driving_time --实际在途时间
          ,max(t.planned_arrival_time ) as planned_arrival_time --计划到车时间
          ,max(t.actual_arrival_time ) as actual_arrival_time --实际到车时间
          ,if(min(t.planned_arrival_time) < max(t.actual_arrival_time),1,0) as is_delay -- 是否延误
          ,sum(t.package_total_weight) as bill_weight -- 快件重量
          ,sum(t.package_inside_charge_weight) as bill_fee_weight -- 计费重量
          ,max(t.send_car_scan_time ) as send_car_scan_time --扫描发车时间
          ,max(t.arrival_car_scan_time) as arrival_car_scan_time --到解车扫描时间
          ,max(t.app_departure_time) as app_departure_time  --司机发车签到时间
          ,max(t.app_arrival_time ) as app_arrival_time  --司机到车签到时间
          ,max(t.package_total_volume) as package_total_volume --实际荷载体积
          ,max(t.load_start_time) as load_start_time --挂箱装车开始时间
          ,max(t.load_end_time ) as load_end_time --挂箱装车结束时间
          ,t.dt
from jms_dwd.dwd_wide_tms_road_waybill_dt t
where t.dt between date_add('{{ execution_date | cst_ds }}', -6) AND '{{ execution_date | cst_ds }}'
      and t.plate_number is not null
group by   t.shipment_id    --任务ID
          ,t.shipment_no --任务编号
          ,t.shipment_name --任务名称
          ,t.start_site_code
          ,t.start_site_name
          ,t.end_site_code
          ,t.end_site_name
          ,t.roadsection_id --路段编号
          ,t.dt
),

shipment_start_end as (
select
       t.shipment_no
      ,sum(t.start_site_out_position) as start_site_out_position --出港方数
      ,sum(t.start_site_out_weight) as start_site_out_weight  --出港重量
      ,sum(t.start_site_out_packagecode_nums) as start_site_out_packagecode_nums  --出港包数
      ,sum(t.start_site_out_package_bill_nums)  as start_site_out_package_bill_nums  --出港包内件数
      ,sum(t.start_site_out_bills_nums)  as start_site_out_bills_nums --出港单票数
      ,sum(t.start_site_out_total) as start_site_out_total--始发中心出港总票数
      ,sum(t.end_site_insert_position) as end_site_insert_position --目的中心进港方数
      ,sum(t.end_site_insert_weight) as end_site_insert_weight --目的中心进港重量
from (
      --始发中心
      select
              t.shipment_no
             ,sum(t.package_total_volume) as start_site_out_position --出港方数
             ,sum(t.package_total_weight) as start_site_out_weight  --出港重量
             ,count(distinct t.packagecode) as start_site_out_packagecode_nums  --出港包数
             ,sum(case when t.packagecode is not null then 1 else 0 end )  as start_site_out_package_bill_nums  --出港包内件数
             ,sum(case when t.packagecode is null then 1 else 0 end )  as start_site_out_bills_nums --出港单票数
             ,count(1) as start_site_out_total--始发中心出港总票数
             ,0 as end_site_insert_position
             ,0 as end_site_insert_weight
      from jms_dwd.dwd_wide_tms_road_waybill_dt t
      where t.roadsection_id=1 and t.dt between date_add('{{ execution_date | cst_ds }}', -6) AND '{{ execution_date | cst_ds }}'
      and t.plate_number is not null
      group by
      t.shipment_no

      union all
      --目的中心
      select
              t.shipment_no
             ,0  as start_site_out_position --出港方数
             ,0  as start_site_out_weight  --出港重量
             ,0  as start_site_out_packagecode_nums  --出港包数
             ,0  as start_site_out_package_bill_nums  --出港包内件数
             ,0  as start_site_out_bills_nums --出港单票数
             ,0  as start_site_out_total--始发中心出港总票数
             ,sum(t.package_total_volume) as end_site_insert_position --进港方数
             ,sum(t.package_total_weight) as end_site_insert_weight  --进港重量
      from jms_dwd.dwd_wide_tms_road_waybill_dt t
      where t.end_code=t.end_site_code and t.dt between date_add('{{ execution_date | cst_ds }}', -6) AND '{{ execution_date | cst_ds }}'
      and t.plate_number is not null
      group by
      t.shipment_no
) t
group by t.shipment_no

)

insert overwrite table jms_dws.dws_wide_tms_vehicle_road_dt partition (dt)
select
 shipment_cars.shipment_id    --任务ID
,shipment_cars.shipment_no --任务编号
,shipment_cars.shipment_name --任务名称
,shipment_stop.start_site_code  --始发中心ID
,shipment_stop.start_site_name --始发中心
,shipment_stop.end_site_code --目的中心ID
,shipment_stop.end_site_name --目的中心
,shipment_stop.roadsection_id --线路排序
,shipment_cars.plate_number--车牌号
,shipment_cars.vehicletype_name --车型
,shipment_stop.road_mileage --规划里程(KM)
,shipment_cars.vehicleline_type --线路类型
,'' as car_type --车辆类型
,'' as run_type --作业类型
,shipment_cars.vehicle_volume --车辆荷载体积
,shipment_cars.load_weight --车辆荷载重量
,'' as truck_team --所属车队
,shipment_cars.carrier_name  --承运商
,shipment_stop.is_emport  --是否空车
,shipment_cars.is_over_time  --是否加班
,'' as returnsign  --返程标识(空放，对跑) 线路名称来区分(线路单边、往返、对开)
,'' as start_site_insert_position  --始发中心进港方数
,'' as start_site_insert_weight --始发中心进港重量
,shipment_start_end.start_site_out_position --始发中心出港方数
,shipment_start_end.start_site_out_weight  --始发中心出港重量
,shipment_start_end.end_site_insert_position  -- 目的中心进港方数
,shipment_start_end.end_site_insert_weight  -- 目的中心进港重量（地磅重量）
,'' as end_site_out_position -- 目的中心出港方数
,'' as end_site_out_weight -- 目的中心出港重量
,shipment_stop.loadrate_weight  --实际装载率（按重量）
,shipment_stop.loadrate_volume --实际装载率（按体积）
,shipment_stop.planned_departure_time --计划发车时间
,shipment_stop.actual_departure_time --实际发车时间
,shipment_stop.plan_driving_time  --计划在途时间
,shipment_stop.actual_driving_time   --实际在途时间
,shipment_stop.planned_arrival_time --计划到车时间
,shipment_stop.actual_arrival_time --实际到车时间
,'' as plan_frequency --计划参加班次
,'' as actual_frequency --实际参加班次
,shipment_stop.is_delay  --是否延误
,shipment_stop.bill_weight -- 快件重量
,shipment_stop.bill_fee_weight -- 计费重量
,shipment_start_end.start_site_out_packagecode_nums as out_bags --出港包数
,shipment_start_end.start_site_out_package_bill_nums  as out_bag_bills  --出港包内件数
,shipment_start_end.start_site_out_bills_nums as  out_bills --出港单票数
,shipment_start_end.start_site_out_total as out_bills_total  --始发中心出港总票数
,'' as is_unloadlate   --是否晚卸
,shipment_stop.send_car_scan_time  --扫描发车时间
,'' as actual_start_date2  --gps实际发车时间  数据不全
,shipment_stop.arrival_car_scan_time  --扫描到车时间
,'' as actual_end_date2 --gps实际到车时间
,'' as insert_weight_date --地磅进港到车时间
,'' as out_weight_date --地磅出港发车时间
,'' as actual_start_date_type --发车时间类型
,'' as actual_end_date_type --到车时间类型
,'' as loadometer_weight --调整后地磅重量
,shipment_cars.trailer_number --挂牌
,'' as max_weight_new --车辆荷载重量（新）
,'' as oil --油耗
,shipment_stop.app_departure_time  --司机发车签到时间
,shipment_stop.app_arrival_time  --司机到车签到时间
,shipment_stop. package_total_volume --实际荷载体积
,'' as forecast_frequency          --预测参加频次:实际发车+计划在途时长（+1h)
,'' as trailer_car_type  --挂车车厢类型
,'' as center_one_disp_date --到车中心一派时间
,'' as center_two_disp_date --到车中心二派时间
,'' as center_three_disp_date --到车中心三派时间

,shipment_cars.shipno_planned_departure_time --始发站计划发车时间
,shipment_cars.shipno_actual_departure_time --始发站发车时间
,shipment_cars.shipno_planned_arrival_time --末端站计划到车时间
,shipment_cars.shipno_actual_arrival_time --末端站到车时间
,shipment_cars.shipment_state --线路批次运行状态 1-未发车 2-发车 3-到车 4-审核 5-结算
,'' as pass_car --过路类型:0-正常 、1-只卸不装 、2-只装不卸、 3-又卸又装、4-卸完又装、5-卸完不装
,shipment_cars.driver_name  --主驾驶员
,shipment_cars.driver1_tel --主驾驶员电话
,'' as driver2  --副驾驶员
,'' as driver2_tel --副驾驶员电话
,shipment_stop.load_start_time --挂箱装车开始时间
,shipment_stop.load_end_time --挂箱装车结束时间
, '' as system_slow_derate_duration --系统减免时长
, '' as manual_slow_derate_duration --人工缓行减免时长
,shipment_stop.dt
from
  shipment_stop shipment_stop
left join shipment_cars shipment_cars on shipment_stop.shipment_no=shipment_cars.shipment_no
left join shipment_start_end shipment_start_end on shipment_stop.shipment_no=shipment_start_end.shipment_no
distribute by shipment_stop.dt
;
